package com.zlyx.easy.core.tool;

import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import com.zlyx.easy.core.list.Lists;
import com.zlyx.easy.core.map.Maps;

/**
 * @desc 描述
 * @author guang
 * @date 2019年8月22日
 */
public class SqlBuilder {

	public enum OpType {
		SELECT, COUNT, SAVE, DELETE, UPDATE;
	}

	public final static Map<SqlBuilder.OpType, String> sqlTemplates;

	public final static String SEPARATOR = ", ";

	static {
		sqlTemplates = Maps.newMap();
		sqlTemplates.put(OpType.SELECT, "SELECT * FROM %s WHERE %s");
		sqlTemplates.put(OpType.COUNT, "SELECT COUNT(*) FROM %s WHERE %s");
		sqlTemplates.put(OpType.SAVE, "REPLACE INTO %s SET %s");
		sqlTemplates.put(OpType.DELETE, "DELETE FROM %s WHERE %s");
		sqlTemplates.put(OpType.UPDATE, "UPDATE %s SET %s");
	}

	/**
	 * 构建包含“=”的SQL
	 * 
	 * @param type      操作类型
	 * @param tableName 表名称
	 * @param params    参数map
	 * @param ignores   忽略的keys
	 * @return
	 */
	public static String buildSQL(OpType type, String tableName, Map<?, ?> params, String... ignoreKeys) {
		String separator = SEPARATOR;
		if (OpType.SELECT == type || OpType.COUNT == type || OpType.DELETE == type) {
			separator = " AND ";
		}
		String condition = appendSQL(params, separator, "=", ignoreKeys);
		String sql = String.format(sqlTemplates.get(type), tableName, condition).trim();
		if (sql.endsWith("WHERE")) {
			sql = sql.substring(0, sql.indexOf("WHERE"));
		}
		return sql.trim();
	}

	/**
	 * 拼接equals参数
	 * 
	 * @param params    参数map
	 * @param separator 分隔符
	 * @param sysbol    连接符(eg:>,<,=等)
	 * @param ignores   忽略的keys
	 * @return
	 */
	public static String appendSQL(Map<?, ?> params, String separator, String sysbol, String... ignoreKeys) {
		EasyBuffer eb = new EasyBuffer();
		List<String> keys = Lists.newList(ignoreKeys);
		for (Entry<?, ?> entry : params.entrySet()) {
			if (!keys.contains(entry.getKey()) && Ops.isNotEmpty(entry.getKey())) {
				eb.append(entry.getKey()).append(" ").append(sysbol).append(" '").append(entry.getValue()).append("' ")
						.append(separator);
			}
		}
		return eb.clearEnd(separator.length());
	}

	/**
	 * 构建包含like语句的SQL
	 * 
	 * @param type        操作类型
	 * @param params      参数map
	 * @param sql         sql
	 * @param includeKeys 包含的keys
	 * @return
	 */
	public static EasyBuffer likeSQL(OpType type, String tableName, Map<?, ?> params, String... likeKeys) {
		EasyBuffer eb = EasyBuffer.newBuffer(SqlBuilder.buildSQL(type, tableName, params, likeKeys));
		for (String key : likeKeys) {
			if (Ops.isNotEmpty(params.get(key))) {
				if (eb.contains("WHERE")) {
					eb = eb.append(" AND ", key, " LIKE '%", params.get(key), "%'");
				} else {
					eb = eb.append(" WHERE ", key, " LIKE '%", params.get(key), "%'");
				}
			}
		}
		return eb;
	}

	/**
	 * @param update
	 * @param table
	 * @param params
	 * @return
	 */
	public static String updateSQL(String table, Map<?, ?> params, String... relyKeys) {
		EasyBuffer eb = EasyBuffer.newBuffer(SqlBuilder.buildSQL(OpType.UPDATE, table, params, relyKeys));
		for (String key : relyKeys) {
			if (Ops.isNotEmpty(params.get(key))) {
				if (eb.contains("WHERE")) {
					eb = eb.append(" AND ", key, " = '", params.get(key), "' ");
				} else {
					eb = eb.append(" WHERE ", key, " = '", params.get(key), "' ");
				}
			}
		}
		return eb.toString();
	}

	/**
	 * 构建排序片段
	 * 
	 * @param pageNumber
	 * @param pageSize
	 * @return
	 */
	public static String order(String name, String sort) {
		return EasyBuffer.wrapper(" ORDER BY ", name, " ", sort);
	}

	/**
	 * 构建分页片段
	 * 
	 * @param pageNumber
	 * @param pageSize
	 * @return
	 */
	public static String page(int pageNumber, int pageSize) {
		int start = (pageNumber - 1) * pageSize;
		int end = start + pageSize;
		return EasyBuffer.wrapper(" LIMIT ", start, ",", end);
	}

	/**
	 * 构建分页片段
	 * 
	 * @param pageNumber
	 * @param pageSize
	 * @return
	 */
	public static String page(String pageNumber, String pageSize) {
		return page(Integer.parseInt(pageNumber), Integer.parseInt(pageSize));
	}

	public static void main(String[] args) {
		Map<String, Object> params = Maps.newMap("name", "fsdfr", "id", 1);
		System.out.println(SqlBuilder.likeSQL(OpType.SELECT, "test", params, "name"));
		System.out.println(SqlBuilder.likeSQL(OpType.COUNT, "test", params, "name"));
		System.out.println(SqlBuilder.likeSQL(OpType.DELETE, "test", params, "name"));
		System.out.println(SqlBuilder.likeSQL(OpType.DELETE, "test", params, "name"));
		System.out.println(SqlBuilder.updateSQL("test", params, "id", "name"));
	}

}
